IF EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'VW_AccountFetch')
	BEGIN
		DROP  View VW_AccountFetch
	END
GO

CREATE View VW_AccountFetch AS

SELECT
	a.AccountId, a.Title, a.Code, a.CreateDate, a.UpdateDate, a.TypeFlags, a.Guid, a.Currency,
	dbo.FN_CleanMoneyValue(ci.Balance) AS ClientCharges,
	dbo.FN_CleanMoneyValue(ciToday.Balance) AS ClientChargesToday,
	dbo.FN_CleanMoneyValue(p.Payments) AS ClientPayments,
	dbo.FN_CleanMoneyValue(pToday.Payments) AS ClientPaymentsToday,
	dbo.FN_CleanMoneyValue(c.Credits) AS ClientCredits,
	dbo.FN_CleanMoneyValue(cToday.Credits) AS ClientCreditsToday
FROM Accounts AS a

-- Client invoices before today
LEFT JOIN (
	SELECT SUM(InvoiceItems.Amount) AS Balance, Invoices.AccountId
	FROM Invoices
	INNER JOIN (
		SELECT SUM(Rate * Quantity) AS Amount, InvoiceId
		FROM InvoiceItems
		GROUP BY InvoiceItemId, InvoiceId
	) AS InvoiceItems ON InvoiceItems.InvoiceId = Invoices.InvoiceId
	WHERE Invoices.InvoiceDate < CONVERT(CHAR(8),GETDATE(),10)
	AND Invoices.TypeFlag = 1
	GROUP BY Invoices.AccountId
) AS ci ON ci.AccountId = a.AccountId

-- Client invoices today
LEFT JOIN (
	SELECT SUM(InvoiceItems.Amount) AS Balance, Invoices.AccountId
	FROM Invoices
	INNER JOIN (
		SELECT SUM(Rate * Quantity) AS Amount, InvoiceId
		FROM InvoiceItems
		GROUP BY InvoiceItemId, InvoiceId
	) AS InvoiceItems ON InvoiceItems.InvoiceId = Invoices.InvoiceId
	WHERE Invoices.InvoiceDate >= CONVERT(CHAR(8),GETDATE(),10)
	AND Invoices.TypeFlag = 1
	GROUP BY Invoices.AccountId
) AS ciToday ON ciToday.AccountId = a.AccountId

-- Payments made before today
LEFT JOIN (
	SELECT SUM(Amount) AS Payments, AccountId
	FROM Payments
	WHERE PaymentDate < CONVERT(CHAR(8),GETDATE(),10)
	GROUP BY AccountId
) AS p ON p.AccountId = a.AccountId

-- Payments made today
LEFT JOIN (
	SELECT SUM(Amount) AS Payments, AccountId
	FROM Payments
	WHERE PaymentDate >= CONVERT(CHAR(8),GETDATE(),10)
	GROUP BY AccountId
) AS pToday ON pToday.AccountId = a.AccountId

-- Credits made before today
LEFT JOIN (
	SELECT SUM(Amount) AS Credits, AccountId
	FROM Credits
	WHERE CreditDate >= CONVERT(CHAR(8),GETDATE(),10)
	GROUP BY AccountId
) AS c ON c.AccountId = a.AccountId

-- Credits made today
LEFT JOIN (
	SELECT SUM(Amount) AS Credits, AccountId
	FROM Credits
	WHERE CreditDate < CONVERT(CHAR(8),GETDATE(),10)
	GROUP BY AccountId
) AS cToday ON cToday.AccountId = a.AccountId

GO